<?php
/**
 * It runs for daily tasks.
 * 
 *   1. Collects the offer report per day.
 *   2. 
 *
 * LBB Solutions Oy 版权所有 © 2013
 */
class TaskDailyCommand extends CConsoleCommand {
	
	/**
	 * @param array $args
	 *   1. offer_report: calcualte offer and total performance per day
	 *   2. seo_merchant: generate seo id for new merchants
	 */
	public function run($args) {
		Yii::app()->setTimeZone('Europe/Helsinki');
		
		$debug = strtolower($args[0]);
		if ($debug != 'prod') {
			// set the differnet DB
			Yii::app()->db->setActive(false);
			Yii::app()->db->connectionString = 'mysql:host=localhost;dbname=yamiifi_dev';
		}
		$task_key = strtolower($args[1]);
		
		if (isset($args[2])) $date_string = strtolower($args[2]);//e.g. 2013-05-28
		if (!empty($date_string)) {
			$local_date = $date_string;
		} else {
			$local_date = date('Y-m-d', mktime(0,0,0, date('m'), date('d')-1));//yesterday
		}
		
		echo date('Y-m-d H:i:s') . ": Start running TaskDailyCommand for {$local_date}: {$task_key}";
		switch ($task_key) {
			case 'offer_report':
				$this->_makeOfferReport($local_date);
				$this->_makeSiteReportDaily($local_date);
				$this->makeVisitReportDaily($local_date);//get the customer visits report
				$this->actionMakeSeoId();//make seo
				break;
			case 'offer_report_all':
				$this->_makeFullOfferReport('2013-02-01', '2013-06-02');
				$this->_makeSiteReportFull('2013-02-01', '2013-06-02');
				break;
			case 'seo_merchant':
				$this->actionMakeSeoId();
				break;
		}
		echo date('Y-m-d H:i:s') . ": End running TaskDailyCommand for {$local_date}: {$task_key}";
	}
	
	/**
	 * Calculate the marketing reports on this local date.
	 * @param string $local_date The date format likes 2013-05-28.
	 */
	private function _makeOfferReport($local_date, $connection = null) {
		$start_time = $local_date . ' 00:00:00';
		$end_time = $local_date . ' 23:59:59';
		
		//fetch all offers valid on this local date
		$inner_connection = false;
		if (null === $connection) {
			$connection = Yii::app()->db;
			$inner_connection = true;
		}
		$sql_offers = ' SELECT id FROM tbl_merchant_special
			WHERE published_time IS NOT NULL
			  AND ( (start_date<=:start_time AND end_date>=:end_time) OR (DATE(start_date)=:local_date OR DATE(end_date)=:local_date) )
			ORDER BY published_time DESC ';
		$command = $connection->createCommand($sql_offers);
		$rows = $command->queryAll(true, array(
			':start_time' => $start_time,
			':end_time' => $end_time,
			':local_date' => $local_date,
		));
		
		//calculate per offer
		foreach ($rows as $r) {
			$id_special = $r['id'];
			
			//all total number
			$sql_total = 'SELECT 
				(SELECT COUNT(id) FROM tbl_merchant_special_viewed WHERE id_special=:id_special AND insert_timestamp BETWEEN :start_time AND :end_time) AS clicks, 
				(SELECT COUNT(id) FROM tbl_user_device_pushing_log WHERE id_special=:id_special AND insert_time BETWEEN :start_time AND :end_time) AS notifications,
				(SELECT COUNT(id) FROM tbl_merchant_special_shared WHERE id_special=:id_special AND insert_timestamp BETWEEN :start_time AND :end_time) AS spread,
				(SELECT COUNT(id) FROM tbl_merchant_special_redeem WHERE id_special=:id_special AND redeemed=2 AND insert_timestamp BETWEEN :start_time AND :end_time) AS redeems ';
			$command = $connection->createCommand($sql_total);
			$total = $command->queryRow(true, array(
				':id_special' => $id_special,
				':start_time' => $start_time,
				':end_time' => $end_time,
			));
			
			//calculate the reach number: 2*clicks + notifications + spread
			$total['reach'] = $total['clicks'] + $total['clicks'] + $total['notifications'] + $total['spread'];
			if ($total['reach'] > 0) {
				//clear the report
				$sql_offer_report_delete = 'DELETE FROM tbl_merchant_special_report_day WHERE id_special=:id_special AND local_date=:local_date ';
				$params = array(
					':id_special' => $id_special,
					':local_date' => $local_date,
				);
				$command = $connection->createCommand($sql_offer_report_delete);
				$command->execute($params);
				//insert report
				$sql_offer_report_insert = 'INSERT INTO tbl_merchant_special_report_day (id_special, local_date, reach, clicks, notifications, spread, redeems)
					VALUES (:id_special, :local_date, :reach, :clicks, :notifications, :spread, :redeems) ';
				$params['reach'] = $total['reach'];
				$params['clicks'] = $total['clicks'];
				$params['notifications'] = $total['notifications'];
				$params['spread'] = $total['spread'];
				$params['redeems'] = $total['redeems'];
				$command = $connection->createCommand($sql_offer_report_insert);
				$command->execute($params);
			}
		}
		if ($inner_connection === false) $connection->active = false;
	}
	
	private function _makeFullOfferReport($start_date, $end_date) {
		$d1 = strtotime($start_date);
		$d2 = strtotime($end_date);
		$days = ceil(($d2 - $d1)/3600/24);
		
		$connection = Yii::app()->db;
		for ($i = 0; $i < $days; $i ++) {
			$local_date = date('Y-m-d', $d1 + $i*3600*24);
			$this->_makeOfferReport($local_date, $connection);
		}
		$connection->active = false;
	}
	
	private function _makeSiteReportFull($start_date, $end_date) {
		$d1 = strtotime($start_date);
		$d2 = strtotime($end_date);
		$days = ceil(($d2 - $d1)/3600/24);
		
		$connection = Yii::app()->db;
		for ($i = 0; $i < $days; $i ++) {
			$local_date = date('Y-m-d', $d1 + $i*3600*24);
			$this->_makeSiteReportDaily($local_date, $connection);
		}
		$connection->active = false;
	}
	
	private function _makeSiteReportDaily($local_date, $connection = null) {
		$start_time = $local_date . ' 00:00:00';
		$end_time = $local_date . ' 23:59:59';
		
		$inner_connection = false;
		if (null === $connection) {
			$connection = Yii::app()->db;
			$inner_connection = true;
		}
		
		//fetch the active users per day
		$sql_site_report = 'SELECT
			(SELECT COUNT(DISTINCT device_token) FROM tbl_user_device_logging WHERE insert_time BETWEEN :start_time AND :end_time) AS devices,
			(SELECT COUNT(id) FROM tbl_merchant_special_viewed WHERE insert_timestamp BETWEEN :start_time AND :end_time) AS clicks,
			(SELECT COUNT(id) FROM tbl_user_device_pushing_log WHERE insert_time BETWEEN :start_time AND :end_time) AS notifications,
			(SELECT COUNT(id) FROM tbl_merchant_special_shared WHERE insert_timestamp BETWEEN :start_time AND :end_time) AS spread,
			(SELECT COUNT(id) FROM tbl_merchant_special_redeem WHERE redeemed=2 AND insert_timestamp BETWEEN :start_time AND :end_time) AS redeems';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_site_report);
		$total = $command->queryRow(true, array(
			':start_time' => $start_time,
			':end_time' => $end_time,
		));
		
		$sql_site_report_delete = 'DELETE FROM tbl_site_daily_report WHERE local_date=:local_date ';
		$params = array(
			':local_date' => $local_date,
		);
		$command = $connection->createCommand($sql_site_report_delete);
		$command->execute($params);
		
		$sql_site_report_insert = 'INSERT INTO tbl_site_daily_report (local_date, devices, clicks, notifications, spread, redeems)
			VALUES (:local_date, :devices, :clicks, :notifications, :spread, :redeems) ';
		$params['devices'] = $total['devices'];
		$params['clicks'] = $total['clicks'];
		$params['notifications'] = $total['notifications'];
		$params['spread'] = $total['spread'];
		$params['redeems'] = $total['redeems'];
		$command = $connection->createCommand($sql_site_report_insert);
		$command->execute($params);
		if ($inner_connection) $connection->active = false;
	}
	
	public function actionMakeSeoId() {
		$sql_new_merchants = 'SELECT m.id, m.name, m.address
			FROM tbl_merchant_account m
			WHERE m.status=1 AND m.seo_id IS NULL ';
		$sql_seo_merchant = 'UPDATE tbl_merchant_account SET seo_id=:seo_id WHERE id=:id';
		$sql_checking_seo = 'SELECT COUNT(id) FROM tbl_merchant_account WHERE seo_id=:seo_id';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_new_merchants);
		$rows = $command->queryAll(true);
		
		foreach ($rows as $r) {
			$seo_id = BUtils::seoUrl($r['name']);
			
			//checking unique seo id
			$command = $connection->createCommand($sql_checking_seo);
			$total = $command->queryScalar(array(':seo_id'=>$seo_id));
			$i = 0;
			while ($total > 0) {
				$i ++;
				$seo_id = $seo_id . '-' . $i;
				$total = $command->queryScalar(array(':seo_id'=>$seo_id));
				if ($i > 10) {
					BUtils::EmailAdmin('SEO ID has more than 10 times', print_r($r));
					break;
				}
			}
			
			$command = $connection->createCommand($sql_seo_merchant);
			$command->execute(array(
				':seo_id' => $seo_id,
				':id' => $r['id'],
			));
		}
		$connection->active = false;
	}
	
	/**
	 * Make the visit daily report.
	 * @param string $date '2013-09-01'
	 */
	public function makeVisitReportDaily($date) {
		$sql_report = 'INSERT INTO tbl_visit_report_daily(id_merchant, local_date, visits, amount)
			SELECT id_merchant, DATE(create_time) AS local_date, COUNT(id) AS visits, SUM(amount) AS amount
			FROM tbl_visit_transaction
			where type=1 AND DATE(create_time)=:local_date
			GROUP BY id_merchant';
		$params = array(
			':local_date' => $date,
		);
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_report);
		$n = $command->execute($params);
		$connection->active = false;
		
		echo "<br />\n{$n} row(s) inserted.<br />\n";
	}
}